
package doc2db.DbManager;

 
import java.sql.*;
import java.util.*;
import doc2db.Keey;

/**
 *
 * @author yuriy
 */
public class DbSelects {
    private  static boolean connected = false;
    private  static Connection conn;

    private Statement statement;
    private ResultSet result_set;
    private ResultSetMetaData meta_data;
    private boolean query_sent = false;



    /**
     * Constructor makes connection to database if not connected yet.
     * @param dbURL (jdbc:postgresql:// + dbURL + : + dbPort + / + dbName)
     * @param properties (user, pswd)
     * @throws java.sql.SQLException Error connecting to database (possibly doesn't exist).
     * @throws java.lang.ClassNotFoundException Error loading JDBC driver.
     */
    public DbSelects(String dbURL, Properties properties) throws SQLException, ClassNotFoundException {
        if (!connected) {
            this.connection(dbURL, properties);
        }
    }

    synchronized void connection(String dbURL, Properties properties) throws ClassNotFoundException, SQLException {
        String driver = "org.postgresql.Driver";
        try {
            //load static section of driver
            Class.forName(driver);
            
            //create connection with db
            conn = DriverManager.getConnection(dbURL, properties);
            setAutoCommit(true);

            //set "utf" encoding on client side - it's necessary to to display data
            Statement query = conn.createStatement();
            query.execute("set client_encoding to 'UNICODE'");

            //if all goes well, set the flag of connections
            connected = true;
        } catch (SQLException e) {
            System.err.println("Troubles while loading PostgreSQL JDBC driver:" + e);
            throw e;
        } catch (ClassNotFoundException e) {
            System.err.println("Troubles - database cannot be accessed:" + e);
            throw e;
        }
    }

    /**
     * Sends the query. Uses for SELECT-queries.
     * @param query String - SQL-statement to execute.
     * @return boolean - true if executed successfully.
     */
    public synchronized boolean sendQuery(String query) {
        try {
            if (query_sent) {
                return false;
            }
            //System.out.println(query);
            statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            result_set = statement.executeQuery(query);
            meta_data = result_set.getMetaData();
            query_sent = true;
            return true;
        } catch (SQLException ex) {
            //System.err.println("Error! - " + ex);
            return false;
        }
    }

    /**
     * Sends the query. Uses for nonSELECT-queries.
     * @param query String - SQL-statement to execute.
     * @return boolean - true if executed successfully.
     */
    public synchronized boolean sendQueryUpdate(String query) {
        try {
            if (query_sent) {
                return false;
            }
            //System.out.println(query);
            statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            statement.executeUpdate(query);
            return true;
        } catch (SQLException ex) {
            //System.err.println("Error! - " + ex);
            return false;
        }
    }

    /**
     * Returns number of columns requested by the query.
     * @return int
     */
    public synchronized int getColumns() {
        if (query_sent) {
            try {
                return meta_data.getColumnCount();
            } catch (SQLException ex) {
                return 0;
            }
        }
        return 0;
    }

    /**
     * Returns number of requested rows.
     * @return int
     */
    public synchronized int getRows() {
        try {
            if (!query_sent) {
                return 0;
            }
            result_set.last();
            return result_set.getRow();
        } catch (Exception ex) {
            return 0;
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public synchronized String getString(int row, int col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public synchronized String getString(int row, String col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            return "";
        }
    }

    /**
     * Returns int value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public synchronized int getInt(int row, int col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            return 0;
        }

    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public synchronized int getInt(int row, String col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            return 0;
        }

    }

    /**
     * Clears query statement.
     */
    public synchronized void clear() {
        try {
            if (query_sent) {
                statement.close();
                query_sent = false;
            }
        } catch (SQLException ex) {
            System.err.println("Error - can closed statement!\n");
            query_sent = false;
        }
    }

    public synchronized void setAutoCommit(boolean autoCommit) {
        try {
            conn.setAutoCommit(autoCommit);
        } catch (SQLException ex) {
            if (Keey.debug) {
                String set = autoCommit ? "set" : "cancel";
                System.err.println("Error - can " + set + " auto commit!\n");
            }
        }
    }

    public synchronized void commit() {
        try {
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            if (Keey.debug) {
                System.err.println("Error - can commit!\n");
            }
        }
    }

    public synchronized void close_connect() {
        try {
            if (connected) {
                conn.close();
                connected = false;
            }
        } catch (Exception ex) {
            System.err.println("Can close connect!\n");
            if (Keey.debug) {
                System.err.println(ex);
            }
        }
    }

    public synchronized boolean isConnected(){
        return connected;
    }

    public synchronized final ResultSetMetaData getMetaData(){
        return meta_data;
    }

    @Override
    protected synchronized void finalize() throws Throwable {
        super.finalize();
        //close_connect();
    }
}
